
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 06/08/2011 01:02:09
-- Generated from EDMX file: D:\Studia\Semestr 8\Gospodarka\Generator-Planu\PlanInterfejs\PlanInterfejs\PlanModelv3.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [plandb];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_Grupa_Nauczyciel]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Grupa] DROP CONSTRAINT [FK_Grupa_Nauczyciel];
GO
IF OBJECT_ID(N'[dbo].[FK_Relation_13]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Zajecia] DROP CONSTRAINT [FK_Relation_13];
GO
IF OBJECT_ID(N'[dbo].[FK_Relation_14]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Zajecia] DROP CONSTRAINT [FK_Relation_14];
GO
IF OBJECT_ID(N'[dbo].[FK_Relation_15]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Zajecia] DROP CONSTRAINT [FK_Relation_15];
GO
IF OBJECT_ID(N'[dbo].[FK_Relation_16]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Plan_Zajec] DROP CONSTRAINT [FK_Relation_16];
GO
IF OBJECT_ID(N'[dbo].[FK_Relation_17]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Plan_Zajec] DROP CONSTRAINT [FK_Relation_17];
GO
IF OBJECT_ID(N'[dbo].[FK_Relation_20]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Sala] DROP CONSTRAINT [FK_Relation_20];
GO
IF OBJECT_ID(N'[dbo].[FK_Relation_21]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Zajecia] DROP CONSTRAINT [FK_Relation_21];
GO
IF OBJECT_ID(N'[dbo].[FK_Relation_3]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Nauczane_Przedmioty] DROP CONSTRAINT [FK_Relation_3];
GO
IF OBJECT_ID(N'[dbo].[FK_Relation_4]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Nauczane_Przedmioty] DROP CONSTRAINT [FK_Relation_4];
GO
IF OBJECT_ID(N'[dbo].[FK_Relation_7]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Wymagane_Przedmioty] DROP CONSTRAINT [FK_Relation_7];
GO
IF OBJECT_ID(N'[dbo].[FK_Sala_Przedmiot_Typy_FK]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Sala] DROP CONSTRAINT [FK_Sala_Przedmiot_Typy_FK];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Budynek]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Budynek];
GO
IF OBJECT_ID(N'[plandbModelStoreContainer].[Dzien]', 'U') IS NOT NULL
    DROP TABLE [plandbModelStoreContainer].[Dzien];
GO
IF OBJECT_ID(N'[dbo].[Godziny_Zajec]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Godziny_Zajec];
GO
IF OBJECT_ID(N'[dbo].[Grupa]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Grupa];
GO
IF OBJECT_ID(N'[dbo].[Nauczane_Przedmioty]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Nauczane_Przedmioty];
GO
IF OBJECT_ID(N'[dbo].[Nauczyciel]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Nauczyciel];
GO
IF OBJECT_ID(N'[dbo].[Plan_Zajec]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Plan_Zajec];
GO
IF OBJECT_ID(N'[dbo].[Przedmiot_Typy]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Przedmiot_Typy];
GO
IF OBJECT_ID(N'[dbo].[Sala]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Sala];
GO
IF OBJECT_ID(N'[dbo].[Wymagane_Przedmioty]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Wymagane_Przedmioty];
GO
IF OBJECT_ID(N'[dbo].[Zajecia]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Zajecia];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Budynek'
CREATE TABLE [dbo].[Budynek] (
    [ID_Budynek] int IDENTITY(1,1) NOT NULL,
    [Nazwa] varchar(30)  NOT NULL,
    [Adres] varchar(40)  NOT NULL
);
GO

-- Creating table 'Godziny_Zajec'
CREATE TABLE [dbo].[Godziny_Zajec] (
    [NumerPorzadkowy] int  NOT NULL,
    [Czas_Poczatek] time  NOT NULL,
    [Czas_Koniec] time  NOT NULL
);
GO

-- Creating table 'Grupa'
CREATE TABLE [dbo].[Grupa] (
    [ID_Grupa] int IDENTITY(1,1) NOT NULL,
    [Rocznik] int  NULL,
    [Poziom_grupy] int  NOT NULL,
    [ID_Nauczyciel] int  NOT NULL,
    [Nazwa] varchar(50)  NOT NULL
);
GO

-- Creating table 'Nauczane_Przedmioty'
CREATE TABLE [dbo].[Nauczane_Przedmioty] (
    [ID_Nauczane_Przedmioty] int IDENTITY(1,1) NOT NULL,
    [ID_Nauczyciel] int  NOT NULL,
    [ID_Przedmiot_Typy] int  NOT NULL
);
GO

-- Creating table 'Nauczyciel'
CREATE TABLE [dbo].[Nauczyciel] (
    [ID_Nauczyciel] int IDENTITY(1,1) NOT NULL,
    [PESEL] int  NULL,
    [Imie] varchar(20)  NOT NULL,
    [Nazwisko] varchar(20)  NOT NULL,
    [Data_Urodzenia] datetime  NULL,
    [E_Mail] varchar(40)  NULL
);
GO

-- Creating table 'Plan_Zajec'
CREATE TABLE [dbo].[Plan_Zajec] (
    [ID_Plan_Zajec] int IDENTITY(1,1) NOT NULL,
    [ID_Zajecia] int  NOT NULL,
    [ID_Grupa] int  NOT NULL
);
GO

-- Creating table 'Przedmiot_Typy'
CREATE TABLE [dbo].[Przedmiot_Typy] (
    [ID_Przedmiot_Typy] int IDENTITY(1,1) NOT NULL,
    [Nazwa] varchar(20)  NOT NULL,
    [Opis] varchar(1)  NULL,
    [WymagaSali] bit  NULL
);
GO

-- Creating table 'Sala'
CREATE TABLE [dbo].[Sala] (
    [ID_Sala] int IDENTITY(1,1) NOT NULL,
    [Numer] varchar(5)  NOT NULL,
    [ID_Budynek] int  NULL,
    [ID_Przedmiot_Typy] int  NULL
);
GO

-- Creating table 'Wymagane_Przedmioty'
CREATE TABLE [dbo].[Wymagane_Przedmioty] (
    [ID_Wymagane_Przedmioty] int IDENTITY(1,1) NOT NULL,
    [Ilosc] int  NOT NULL,
    [Poziom_Grupy] int  NOT NULL,
    [ID_Przedmiot_Typy] int  NOT NULL
);
GO

-- Creating table 'Zajecia'
CREATE TABLE [dbo].[Zajecia] (
    [ID_Zajecia] int IDENTITY(1,1) NOT NULL,
    [ID_Godziny_Zajec] int  NOT NULL,
    [ID_Sala] int  NOT NULL,
    [ID_Przedmiot_Typy] int  NOT NULL,
    [ID_Nauczyciel] int  NOT NULL,
    [ID_Dzien] int  NOT NULL
);
GO

-- Creating table 'Dzien'
CREATE TABLE [dbo].[Dzien] (
    [ID_Dnia] int IDENTITY(1,1) NOT NULL,
    [Nr] int  NOT NULL,
    [Nazwa] nvarchar(15)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [ID_Budynek] in table 'Budynek'
ALTER TABLE [dbo].[Budynek]
ADD CONSTRAINT [PK_Budynek]
    PRIMARY KEY CLUSTERED ([ID_Budynek] ASC);
GO

-- Creating primary key on [NumerPorzadkowy] in table 'Godziny_Zajec'
ALTER TABLE [dbo].[Godziny_Zajec]
ADD CONSTRAINT [PK_Godziny_Zajec]
    PRIMARY KEY CLUSTERED ([NumerPorzadkowy] ASC);
GO

-- Creating primary key on [ID_Grupa] in table 'Grupa'
ALTER TABLE [dbo].[Grupa]
ADD CONSTRAINT [PK_Grupa]
    PRIMARY KEY CLUSTERED ([ID_Grupa] ASC);
GO

-- Creating primary key on [ID_Nauczane_Przedmioty] in table 'Nauczane_Przedmioty'
ALTER TABLE [dbo].[Nauczane_Przedmioty]
ADD CONSTRAINT [PK_Nauczane_Przedmioty]
    PRIMARY KEY CLUSTERED ([ID_Nauczane_Przedmioty] ASC);
GO

-- Creating primary key on [ID_Nauczyciel] in table 'Nauczyciel'
ALTER TABLE [dbo].[Nauczyciel]
ADD CONSTRAINT [PK_Nauczyciel]
    PRIMARY KEY CLUSTERED ([ID_Nauczyciel] ASC);
GO

-- Creating primary key on [ID_Plan_Zajec] in table 'Plan_Zajec'
ALTER TABLE [dbo].[Plan_Zajec]
ADD CONSTRAINT [PK_Plan_Zajec]
    PRIMARY KEY CLUSTERED ([ID_Plan_Zajec] ASC);
GO

-- Creating primary key on [ID_Przedmiot_Typy] in table 'Przedmiot_Typy'
ALTER TABLE [dbo].[Przedmiot_Typy]
ADD CONSTRAINT [PK_Przedmiot_Typy]
    PRIMARY KEY CLUSTERED ([ID_Przedmiot_Typy] ASC);
GO

-- Creating primary key on [ID_Sala] in table 'Sala'
ALTER TABLE [dbo].[Sala]
ADD CONSTRAINT [PK_Sala]
    PRIMARY KEY CLUSTERED ([ID_Sala] ASC);
GO

-- Creating primary key on [ID_Wymagane_Przedmioty] in table 'Wymagane_Przedmioty'
ALTER TABLE [dbo].[Wymagane_Przedmioty]
ADD CONSTRAINT [PK_Wymagane_Przedmioty]
    PRIMARY KEY CLUSTERED ([ID_Wymagane_Przedmioty] ASC);
GO

-- Creating primary key on [ID_Zajecia] in table 'Zajecia'
ALTER TABLE [dbo].[Zajecia]
ADD CONSTRAINT [PK_Zajecia]
    PRIMARY KEY CLUSTERED ([ID_Zajecia] ASC);
GO

-- Creating primary key on [ID_Dnia] in table 'Dzien'
ALTER TABLE [dbo].[Dzien]
ADD CONSTRAINT [PK_Dzien]
    PRIMARY KEY CLUSTERED ([ID_Dnia] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [ID_Budynek] in table 'Sala'
ALTER TABLE [dbo].[Sala]
ADD CONSTRAINT [FK_Relation_20]
    FOREIGN KEY ([ID_Budynek])
    REFERENCES [dbo].[Budynek]
        ([ID_Budynek])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Relation_20'
CREATE INDEX [IX_FK_Relation_20]
ON [dbo].[Sala]
    ([ID_Budynek]);
GO

-- Creating foreign key on [ID_Godziny_Zajec] in table 'Zajecia'
ALTER TABLE [dbo].[Zajecia]
ADD CONSTRAINT [FK_Relation_21]
    FOREIGN KEY ([ID_Godziny_Zajec])
    REFERENCES [dbo].[Godziny_Zajec]
        ([NumerPorzadkowy])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Relation_21'
CREATE INDEX [IX_FK_Relation_21]
ON [dbo].[Zajecia]
    ([ID_Godziny_Zajec]);
GO

-- Creating foreign key on [ID_Nauczyciel] in table 'Grupa'
ALTER TABLE [dbo].[Grupa]
ADD CONSTRAINT [FK_Grupa_Nauczyciel]
    FOREIGN KEY ([ID_Nauczyciel])
    REFERENCES [dbo].[Nauczyciel]
        ([ID_Nauczyciel])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Grupa_Nauczyciel'
CREATE INDEX [IX_FK_Grupa_Nauczyciel]
ON [dbo].[Grupa]
    ([ID_Nauczyciel]);
GO

-- Creating foreign key on [ID_Nauczyciel] in table 'Nauczane_Przedmioty'
ALTER TABLE [dbo].[Nauczane_Przedmioty]
ADD CONSTRAINT [FK_Relation_3]
    FOREIGN KEY ([ID_Nauczyciel])
    REFERENCES [dbo].[Nauczyciel]
        ([ID_Nauczyciel])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Relation_3'
CREATE INDEX [IX_FK_Relation_3]
ON [dbo].[Nauczane_Przedmioty]
    ([ID_Nauczyciel]);
GO

-- Creating foreign key on [ID_Przedmiot_Typy] in table 'Nauczane_Przedmioty'
ALTER TABLE [dbo].[Nauczane_Przedmioty]
ADD CONSTRAINT [FK_Relation_4]
    FOREIGN KEY ([ID_Przedmiot_Typy])
    REFERENCES [dbo].[Przedmiot_Typy]
        ([ID_Przedmiot_Typy])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Relation_4'
CREATE INDEX [IX_FK_Relation_4]
ON [dbo].[Nauczane_Przedmioty]
    ([ID_Przedmiot_Typy]);
GO

-- Creating foreign key on [ID_Nauczyciel] in table 'Zajecia'
ALTER TABLE [dbo].[Zajecia]
ADD CONSTRAINT [FK_Relation_15]
    FOREIGN KEY ([ID_Nauczyciel])
    REFERENCES [dbo].[Nauczyciel]
        ([ID_Nauczyciel])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Relation_15'
CREATE INDEX [IX_FK_Relation_15]
ON [dbo].[Zajecia]
    ([ID_Nauczyciel]);
GO

-- Creating foreign key on [ID_Zajecia] in table 'Plan_Zajec'
ALTER TABLE [dbo].[Plan_Zajec]
ADD CONSTRAINT [FK_Relation_16]
    FOREIGN KEY ([ID_Zajecia])
    REFERENCES [dbo].[Zajecia]
        ([ID_Zajecia])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Relation_16'
CREATE INDEX [IX_FK_Relation_16]
ON [dbo].[Plan_Zajec]
    ([ID_Zajecia]);
GO

-- Creating foreign key on [ID_Przedmiot_Typy] in table 'Zajecia'
ALTER TABLE [dbo].[Zajecia]
ADD CONSTRAINT [FK_Relation_14]
    FOREIGN KEY ([ID_Przedmiot_Typy])
    REFERENCES [dbo].[Przedmiot_Typy]
        ([ID_Przedmiot_Typy])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Relation_14'
CREATE INDEX [IX_FK_Relation_14]
ON [dbo].[Zajecia]
    ([ID_Przedmiot_Typy]);
GO

-- Creating foreign key on [ID_Przedmiot_Typy] in table 'Wymagane_Przedmioty'
ALTER TABLE [dbo].[Wymagane_Przedmioty]
ADD CONSTRAINT [FK_Relation_7]
    FOREIGN KEY ([ID_Przedmiot_Typy])
    REFERENCES [dbo].[Przedmiot_Typy]
        ([ID_Przedmiot_Typy])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Relation_7'
CREATE INDEX [IX_FK_Relation_7]
ON [dbo].[Wymagane_Przedmioty]
    ([ID_Przedmiot_Typy]);
GO

-- Creating foreign key on [ID_Przedmiot_Typy] in table 'Sala'
ALTER TABLE [dbo].[Sala]
ADD CONSTRAINT [FK_Sala_Przedmiot_Typy_FK]
    FOREIGN KEY ([ID_Przedmiot_Typy])
    REFERENCES [dbo].[Przedmiot_Typy]
        ([ID_Przedmiot_Typy])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Sala_Przedmiot_Typy_FK'
CREATE INDEX [IX_FK_Sala_Przedmiot_Typy_FK]
ON [dbo].[Sala]
    ([ID_Przedmiot_Typy]);
GO

-- Creating foreign key on [ID_Sala] in table 'Zajecia'
ALTER TABLE [dbo].[Zajecia]
ADD CONSTRAINT [FK_Relation_13]
    FOREIGN KEY ([ID_Sala])
    REFERENCES [dbo].[Sala]
        ([ID_Sala])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Relation_13'
CREATE INDEX [IX_FK_Relation_13]
ON [dbo].[Zajecia]
    ([ID_Sala]);
GO

-- Creating foreign key on [ID_Dzien] in table 'Zajecia'
ALTER TABLE [dbo].[Zajecia]
ADD CONSTRAINT [FK_Zajecia_Dzien]
    FOREIGN KEY ([ID_Dzien])
    REFERENCES [dbo].[Dzien]
        ([ID_Dnia])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Zajecia_Dzien'
CREATE INDEX [IX_FK_Zajecia_Dzien]
ON [dbo].[Zajecia]
    ([ID_Dzien]);
GO

-- Creating foreign key on [ID_Grupa] in table 'Plan_Zajec'
ALTER TABLE [dbo].[Plan_Zajec]
ADD CONSTRAINT [FK_Relation_171]
    FOREIGN KEY ([ID_Grupa])
    REFERENCES [dbo].[Grupa]
        ([ID_Grupa])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Relation_171'
CREATE INDEX [IX_FK_Relation_171]
ON [dbo].[Plan_Zajec]
    ([ID_Grupa]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------